import java.sql.{Connection, DriverManager, Statement}

object MySQLExample {
  def main(args: Array[String]): Unit = {
    // MySQL连接信息
    val url = "jdbc:mysql://localhost:3306/sparksql_practice"
    val driver = "com.mysql.cj.jdbc.Driver"
    val username = "lsz"
    val password = "lsz"

    var connection: Connection = null
    var statement: Statement = null

    try {
      // 加载MySQL驱动程序
      Class.forName(driver)

      // 建立连接
      connection = DriverManager.getConnection(url, username, password)
      statement = connection.createStatement

      // 创建数据库
      statement.executeUpdate("CREATE DATABASE IF NOT EXISTS sparksql_practice")

      // 使用数据库
      statement.executeUpdate("USE sparksql_practice")

      // 创建学生表
      statement.executeUpdate(
        """CREATE TABLE IF NOT EXISTS students (
          |    student_id INT AUTO_INCREMENT PRIMARY KEY,
          |    first_name VARCHAR(50),
          |    last_name VARCHAR(50),
          |    age INT,
          |    major VARCHAR(100),
          |    enrollment_date DATE
          |)""".stripMargin
      )

      // 填充实验数据
      statement.executeUpdate(
        """INSERT INTO students (first_name, last_name, age, major, enrollment_date) VALUES
          |('Alice', 'Smith', 20, 'Computer Science', '2022-09-01'),
          |('Bob', 'Johnson', 22, 'Electrical Engineering', '2021-08-15'),
          |('Charlie', 'Williams', 21, 'Mechanical Engineering', '2023-01-20'),
          |('David', 'Brown', 19, 'Biology', '2024-03-10'),
          |('Emma', 'Jones', 20, 'Physics', '2022-06-05')
          |""".stripMargin
      )

      println("数据填充完成.")

    } catch {
      case e: Exception => e.printStackTrace()
    } finally {
      // 关闭连接和声明
      statement.close()
      connection.close()
    }
  }
}

